SQL与SQLite的关系划分-RelationalDivisioninSQLwithSQLite

I'm trying to do a relational division on the following table (sorry for the Dutch phrasing) using this document and this question.

我正在尝试使用此文档和此问题对下表（对于荷兰语措辞感到遗憾）进行关系划分。

    voedsel_id  dier_id
    1005        1006
    1004        1006
    1005        1005
    1005        1004
    1004        1004
    1001        1003
    1001        1002
    1001        1001

Which let to the following query for what I want to do.

以下是我想要做什么的查询。

SELECT DISTINCT dier_id
FROM Dieren_voedsel AS X
WHERE NOT EXISTS(
    SELECT voedsel_id
    FROM Dieren_voedsel AS Y
    WHERE Y.dier_id = 1006 AND NOT EXISTS(
        SELECT voedsel_id
        FROM Dieren_voedsel AS Z
        WHERE X.voedsel_id = Z.voedsel_id AND Y.dier_id = Z.dier_id
    )
)
ORDER BY dier_id;

Which let to the following unexpected output.

这让以下意外输出。

dier_id
1004
1005
1006

I thought this division would output all the dier_id tuples that have exactly the same voedsel_id values as dier_id 1006. However, it includes dier_id 1005 which only has voedsel_id 1005 and dier_id 1006 has voedsel_id 1004 and 1005. I thought this would only return dier_id 1004 and 1006. Is there something I did wrong or do I not know how a relational division works?

我认为这个分区会输出所有与dier_id 1006具有完全相同的voedsel_id值的dier_id元组。但是，它包括dier_id 1005，它只有voedsel_id 1005而dier_id 1006有voedsel_id 1004和1005.我认为这只会返回dier_id 1004和1006.我做错了什么或不知道关系师如何运作？

1 个解决方案

SELECT DISTINCT dier_id
FROM Dieren_voedsel AS X
WHERE NOT EXISTS(
    SELECT *
    FROM Dieren_voedsel AS Y
    WHERE Y.dier_id = 1006
      AND NOT EXISTS(
        SELECT *
        FROM Dieren_voedsel AS Z
        WHERE Z.dier_id = X.dier_id
          AND Z.voedsel_id = Y.voedsel_id
    )
)
ORDER BY dier_id;